﻿using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using Yoze.Common.Specifications;

namespace Yoze.Common.Querying
{
    public abstract class TableDataGateway : ITableDataGateway
    {
        private static readonly log4net.ILog log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType.Name);

        /// <summary>
        /// Creates a new instance of the where clause builder.
        /// </summary>
        /// <typeparam name="TTableObject">The type of the object.</typeparam>
        /// <returns>The instance of the where clause builder.</returns>
        protected abstract WhereClauseBuilder<TTableObject> CreateWhereClauseBuilder<TTableObject>()
            where TTableObject : class, new();

        /// <summary>
        /// Creates the database connection.
        /// </summary>
        /// <returns>The <see cref="System.Data.Common.DbConnection"/> instance which represents
        /// the open connection to the relational database.</returns>
        protected abstract DbConnection CreateDatabaseConnection();

        /// <summary>
        /// Creates a database parameter object.
        /// </summary>
        /// <returns>The instance of database parameter object.</returns>
        protected abstract DbParameter CreateParameter();

        /// <summary>
        /// Creates a instance of the command object.
        /// </summary>
        /// <param name="sql">The SQL statement used for creating the command object.</param>
        /// <param name="connection">The <see cref="System.Data.Common.DbConnection"/> which represents
        /// the database connection.</param>
        /// <returns>The instance of the command object.</returns>
        protected abstract DbCommand CreateCommand(string sql, DbConnection connection);

        protected virtual string GetAllFieldNames<TTableObject>(bool excludeAutoGeneratedKeys = false)
            where TTableObject : class, new() => string.Join(", ", typeof(TTableObject)
                .GetProperties(BindingFlags.Public | BindingFlags.Instance | BindingFlags.GetProperty | BindingFlags.SetProperty)
                .Where(p => !excludeAutoGeneratedKeys || !p.IsDefined(typeof(KeyAttribute)) || !p.GetCustomAttribute<KeyAttribute>().AutoGenerated)
                .Select(p => p.Name).ToArray());


        /// <summary>
        /// Gets a comma separated list of the field parameter names for INSERT operation, by using the
        /// specified fields.
        /// </summary>
        /// <typeparam name="TTableObject">The type of the object.</typeparam>
        /// <param name="fields">The <c>PropertyBag</c> object which contains the fields.</param>
        /// <returns>The comma separated list of the field parameter names for INSERT operation.</returns>
        /// <remarks>The auto-generated identity fields will be omitted.</remarks>
        protected virtual string GetInsertParameterNameList<TTableObject>(WhereClauseBuilder<TTableObject> whereClauseBuilder)
            where TTableObject : class, new()
        {
            return string.Join(", ", typeof(TTableObject)
                .GetProperties(BindingFlags.Public | BindingFlags.Instance | BindingFlags.GetProperty | BindingFlags.SetProperty)
                .Where(p => !p.IsDefined(typeof(KeyAttribute)) || !p.GetCustomAttribute<KeyAttribute>().AutoGenerated)
                .Select(p => $"{whereClauseBuilder.ParameterChar}{p.Name.ToLower()}"));
        }

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="TTableObject"></typeparam>
        /// <param name="obj"></param>
        /// <param name="whereClauseBuilder"></param>
        /// <returns></returns>
        protected virtual IEnumerable<DbParameter> GetInsertDbParameterList<TTableObject>(TTableObject obj, WhereClauseBuilder<TTableObject> whereClauseBuilder)
            where TTableObject : class, new()
        {
            var parameters = new List<DbParameter>();
            typeof(TTableObject)
                .GetProperties(BindingFlags.Public | BindingFlags.Instance | BindingFlags.GetProperty | BindingFlags.SetProperty)
                .Where(p => !p.IsDefined(typeof(KeyAttribute)) || !p.GetCustomAttribute<KeyAttribute>().AutoGenerated)
                .ToList()
                .ForEach(p =>
                {
                    var parameter = this.CreateParameter();
                    parameter.ParameterName = $"{whereClauseBuilder.ParameterChar}{p.Name.ToLower()}";
                    parameter.Value = p.GetValue(obj);
                    parameters.Add(parameter);
                });
            return parameters;
        }

        protected virtual string GetUpdateCriteriaParameterNameList<TTableObject>(WhereClauseBuilder<TTableObject> whereClauseBuilder, UpdateCriteria<TTableObject> updateCriteria)
            where TTableObject : class, new()
        {
            return string.Join(", ", updateCriteria.Select(uc => $"{uc.Key}={whereClauseBuilder.ParameterChar}u_{uc.Key}"));
        }

        protected virtual IEnumerable<DbParameter> GetUpdateCriteriaParameterList<TTableObject>(UpdateCriteria<TTableObject> updateCriteria)
            where TTableObject : class, new()
        {
            foreach (var criteria in updateCriteria)
            {
                var key = $"u_{criteria.Key}";
                var dbParameter = this.CreateParameter();
                dbParameter.ParameterName = key;
                dbParameter.Value = criteria.Value;
                yield return dbParameter;
            }
        }

        protected virtual string GetTableName<TTableObject>()
            where TTableObject : class, new()
        {
            if (typeof(TTableObject).IsDefined(typeof(ToTableAttribute)))
            {
                return typeof(TTableObject).GetCustomAttribute<ToTableAttribute>().Name;
            }
            return typeof(TTableObject).Name;
        }

        /// <summary>
        /// Gets a list of database parameters for constructing the selection criteria clause.
        /// </summary>
        /// <typeparam name="T">The type of the object.</typeparam>
        /// <param name="parameterValues">The <c>Dictionary&lt;string, object&gt;</c> instance which contains the criteria.</param>
        /// <returns>The list of database parameters.</returns>
        protected virtual List<DbParameter> GetSelectCriteriaDbParameterList<T>(Dictionary<string, object> parameterValues)
            where T : class, new()
        {
            List<DbParameter> collection = new List<DbParameter>();
            foreach (var kvp in parameterValues)
            {
                DbParameter param = CreateParameter();
                param.ParameterName = kvp.Key;
                param.Value = kvp.Value;
                collection.Add(param);
            }
            return collection;
        }

        /// <summary>
        /// Creates the data object instance from a <see cref="System.Data.Common.DbDataReader"/> instance.
        /// </summary>
        /// <typeparam name="TTableObject">The type of the object to be created.</typeparam>
        /// <param name="reader">The <see cref="System.Data.Common.DbDataReader"/> instance.</param>
        /// <returns>The data object instance.</returns>
        protected virtual TTableObject CreateFromReader<TTableObject>(DbDataReader reader)
            where TTableObject : class, new()
        {
            TTableObject t = new TTableObject();

            typeof(TTableObject)
                .GetProperties(BindingFlags.Public | BindingFlags.Instance | BindingFlags.GetProperty | BindingFlags.SetProperty)
                .ToList()
                .ForEach(pi =>
                {
                    var value = reader[pi.Name];
                    if (value != DBNull.Value)
                    {
                        if (pi.PropertyType == typeof(Guid))
                            pi.SetValue(t, new Guid(Convert.ToString(reader[pi.Name])), null);
                        else
                            pi.SetValue(t, reader[pi.Name], null);
                    }
                });

            return t;
        }

        public async Task InsertAsync<TTableObject>(IEnumerable<TTableObject> tableObjects) where TTableObject : class, new()
        {
            var whereClauseBuilder = this.CreateWhereClauseBuilder<TTableObject>();
            using (var connection = this.CreateDatabaseConnection())
            {
                await connection.OpenAsync();
                using (var transaction = connection.BeginTransaction())
                {
                    try
                    {
                        var sql = $"INSERT INTO {GetTableName<TTableObject>()} ({GetAllFieldNames<TTableObject>(true)}) VALUES ({GetInsertParameterNameList<TTableObject>(whereClauseBuilder)})";
                        using (var command = this.CreateCommand(sql, connection))
                        {
                            command.Transaction = transaction;
                            foreach (var tableObject in tableObjects)
                            {
                                command.Parameters.Clear();
                                var parameters = this.GetInsertDbParameterList<TTableObject>(tableObject, whereClauseBuilder);
                                foreach (var parameter in parameters)
                                {
                                    command.Parameters.Add(parameter);
                                }
                                await command.ExecuteNonQueryAsync();
                            }
                        }
                        transaction.Commit();
                        System.Diagnostics.Trace.WriteLine($"TableDataGatway InsertAsync sql:{sql}");
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        log.Error("Failed to insert the data.", ex);
                    }
                }

            }
        }

        public async Task<IEnumerable<TTableObject>> SelectAsync<TTableObject>(Specification<TTableObject> specification) where TTableObject : class, new()
        {
            try
            {
                using (var connection = this.CreateDatabaseConnection())
                {
                    await connection.OpenAsync();

                    var sql = $"SELECT {this.GetAllFieldNames<TTableObject>()} FROM {this.GetTableName<TTableObject>()}";
                    WhereClauseBuildResult whereClauseBuildResult = null;
                    if (specification != null)
                    {
                        var whereClauseBuilder = this.CreateWhereClauseBuilder<TTableObject>();
                        whereClauseBuildResult = whereClauseBuilder.BuildWhereClause(specification);
                        sql += $" WHERE {whereClauseBuildResult.WhereClause}";
                    }

                    using (var command = this.CreateCommand(sql, connection))
                    {
                        if (whereClauseBuildResult != null)
                        {
                            command.Parameters.Clear();
                            var parameters = this.GetSelectCriteriaDbParameterList<TTableObject>(whereClauseBuildResult.ParameterValues);
                            foreach (var param in parameters)
                            {
                                command.Parameters.Add(param);
                            }
                        }
                        var reader = await command.ExecuteReaderAsync();
                        List<TTableObject> result = new List<TTableObject>();
                        while (await reader.ReadAsync())
                        {
                            result.Add(this.CreateFromReader<TTableObject>(reader));
                        }
                        reader.Close();

                        System.Diagnostics.Trace.WriteLine($"TableDataGatway SelectAsync sql:{sql}");

                        return result;
                    }
                }
            }
            catch(Exception ex)
            {
                log.Error("Failed to select the data.", ex);
                throw;
            }
        }

        public async Task UpdateAsync<TTableObject>(UpdateCriteria<TTableObject> updateCriteria, Specification<TTableObject> specification) where TTableObject : class, new()
        {
            using (var connection = this.CreateDatabaseConnection())
            {
                await connection.OpenAsync();
                await DoUpdateAsync(connection, null, updateCriteria, specification);
            }
        }

        public async Task UpdateAsync<TTableObject>(IEnumerable<Tuple<UpdateCriteria<TTableObject>, Specification<TTableObject>>> batch) where TTableObject : class, new()
        {
            using (var connection = this.CreateDatabaseConnection())
            {
                await connection.OpenAsync();
                using (var transaction = connection.BeginTransaction())
                {
                    try
                    {
                        foreach(var updating in batch)
                        {
                            await this.DoUpdateAsync(connection, transaction, updating.Item1, updating.Item2);
                        }
                        transaction.Commit();
                    }
                    catch(Exception ex)
                    {
                        transaction.Rollback();
                        log.Error("Failed to update.", ex);
                    }
                }
            }
        }

        private async Task DoUpdateAsync<TTableObject>(DbConnection connection, DbTransaction transaction, UpdateCriteria<TTableObject> updateCriteria, Specification<TTableObject> specification) where TTableObject : class, new()
        {
            try
            {
                var whereClauseBuilder = this.CreateWhereClauseBuilder<TTableObject>();
                var sql = $"UPDATE {GetTableName<TTableObject>()} SET {GetUpdateCriteriaParameterNameList<TTableObject>(whereClauseBuilder, updateCriteria)}";
                WhereClauseBuildResult whereClauseBuildResult = null;
                if (specification != null)
                {
                    whereClauseBuildResult = whereClauseBuilder.BuildWhereClause(specification);
                    sql = $"{sql} WHERE {whereClauseBuildResult.WhereClause}";
                }
                using (var command = this.CreateCommand(sql, connection))
                {
                    if (transaction != null)
                    {
                        command.Transaction = transaction;
                    }
                    command.Parameters.Clear();
                    var updateParameters = GetUpdateCriteriaParameterList<TTableObject>(updateCriteria);
                    foreach (var parameter in updateParameters)
                    {
                        command.Parameters.Add(parameter);
                    }
                    if (whereClauseBuildResult != null)
                    {
                        foreach (var kvp in whereClauseBuildResult.ParameterValues)
                        {
                            var parameter = this.CreateParameter();
                            parameter.ParameterName = kvp.Key;
                            parameter.Value = kvp.Value;
                            command.Parameters.Add(parameter);
                        }
                    }
                    await command.ExecuteNonQueryAsync();
                }
                System.Diagnostics.Trace.WriteLine($"TableDataGatway DoUpdateAsync sql:{sql}");
            }
            catch(Exception ex)
            {
                log.Error("Failed to update.", ex);
            }
        }
    }
}
